IF OBJECT_ID('dbo.vwInfoEdCSCRDeliverables') IS NOT NULL
BEGIN
    DROP VIEW dbo.vwInfoEdCSCRDeliverables
    IF OBJECT_ID('dbo.vwInfoEdCSCRDeliverables') IS NOT NULL
        PRINT '<<< FAILED DROPPING VIEW dbo.vwInfoEdCSCRDeliverables >>>'
    ELSE
        PRINT '<<< DROPPED VIEW dbo.vwInfoEdCSCRDeliverables >>>'
END
go
-- =======================================================================
-- Object Name: (VIEW) dbo.vwInfoEdCSCRDeliverables
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 9/13/2007 
--
-- Description: View to Deliverable Statuses, for CSCR Reports.
--
-- Used BY Procedure/Report: (MOST)
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 09/13/07    KC        Created
-- 03/26/08    KC        Added PARTNERS_P_REPORTCAT_34 (CSCR Subcontract)
-- 05/13/08    KC        Added PARTNERS_P_REPORTCAT_42, (Working Budget Amendment)
--                       and PARTNERS_P_REPORTCAT_36 (SubAmendment)
--                       and PARTNERS_P_REPORTCAT_32 (Agreement)
--
-- =======================================================================
CREATE VIEW dbo.vwInfoEdCSCRDeliverables
AS
      
SELECT    D.prop_no,
          D.ReportId,
          D.ReportName,
          RTRIM(C1.code_desc) AS REPORTCAT,
          SUBSTRING(D.REPORTCAT,LEN('PARTNERS_P_')+1,100) AS REPORTCAT_CODE,
          C2.code_desc AS REPORTTYPE,
          D.CompletedDate,
          CASE WHEN S.REPORTSTAT = 'PARTNERS_P_REPORTSTAT_61' AND D.CompletedDate IS NOT NULL
               -- Convert to CSCR - Completed
               THEN 'CSCR - Completed'
               ELSE RTRIM(C3.code_desc)
          END AS REPORTSTAT,
          SUBSTRING(S.REPORTSTAT,LEN('PARTNERS_P_')+1,100) AS REPORTSTAT_CODE,
          S.StatusDate,
          -- For SubContracts, FolderNumber should be ReportName...
          CASE WHEN D.REPORTCAT = 'PARTNERS_P_REPORTCAT_34' 
               THEN D.ReportName + ': ' + S.Comments
               ELSE S.Comments
          END AS Comments,
          CASE WHEN D.SubmittedDate IS NOT NULL
               THEN 1
               ELSE 0
          END AS ConvertedInd,
          D.DeliverableID,
          S.ProcessedDate
FROM      dbo.InfoEdPTDeliverable D LEFT OUTER JOIN dbo.InfoEdCodetab C1 ON D.REPORTCAT = C1.codeID
               LEFT OUTER JOIN dbo.InfoEdCodetab C2 ON D.REPORTTYPE = C2.codeID
               LEFT OUTER JOIN dbo.InfoEdPTDeliverableStatus S ON D.DeliverableID = S.DeliverableID
               LEFT OUTER JOIN dbo.InfoEdCodetab C3 ON S.REPORTSTAT = C3.codeID
WHERE     D.Inst_Code = 'PARTNERS' AND D.REPORTCAT IN ('PARTNERS_P_REPORTCAT_18','PARTNERS_P_REPORTCAT_19',
                    'PARTNERS_P_REPORTCAT_30','PARTNERS_P_REPORTCAT_33','PARTNERS_P_REPORTCAT_34',
                    'PARTNERS_P_REPORTCAT_36','PARTNERS_P_REPORTCAT_42','PARTNERS_P_REPORTCAT_32')
     
go
IF OBJECT_ID('dbo.vwInfoEdCSCRDeliverables') IS NOT NULL
    PRINT '<<< CREATED VIEW dbo.vwInfoEdCSCRDeliverables >>>'
ELSE
    PRINT '<<< FAILED CREATING VIEW dbo.vwInfoEdCSCRDeliverables >>>'
go
